package com.bdmc.smokesensor.services;

import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.bdmc.smokesensor.dao.Smk_AlarmJPA;
import com.bdmc.smokesensor.entity.Smk_Alarm;
import com.bdmc.smokesensor.iservices.ISmk_AlarmService;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.stereotype.Service;

@Service
public class Smk_AlarmService extends BaseService<Smk_Alarm> implements ISmk_AlarmService {

    @Autowired
    private Smk_AlarmJPA _alarmJpa;

    @Override
    public List<Smk_Alarm> findAll() {
        return _alarmJpa.findAll();
    }

    @Override
    public Map<String, Object> findByPageable(int depid, int alarmType, int alarmStat, String start, String end,
            int gc_id, int page, int size) {
        String sql = "select al.id,al.smkDevicesId,al.alarmType,al.alarmDetail,al.alarmStat,al.oid,al.dealType,al.dealMethod,al.dealResult ,"
        +" DATE_FORMAT(al.alarmTime,'%Y-%m-%d %H:%i:%S') alarmDate,DATE_FORMAT(al.joinDate,'%Y-%m-%d %H:%i:%S') joinDate,"
        +"DATE_FORMAT(al.updateDate,'%Y-%m-%d %H:%i:%S') updateDate,al.isDel,al.joinBy,al.updateBy, dv.devName,dv.devId,dv.imei,bd.name as bdName,bd.addr as bdAddr,bd.managerName,bd.managerMobile,gc.groupName "
                + " from smk_alarm al left join smk_devices dv on al.smkDevicesId=dv.id "
                + "  left join sys_department dep on dv.departmentId=dep.id "
                + " left join smk_buildings bd on bd.id = dv.smkBuildingId "
                + " left join smk_groupcompany gc on gc.id = bd.smkGroupCompanyId" + " where dep.id=" + depid
                + " or dep.treeRoot like '%," + depid + ",%' ";
        if (alarmType > 0)
            sql += " and al.alarmType= " + alarmType;

        if (alarmStat > 0)
            sql += " and al.alarmStat= " + alarmStat;
        if (gc_id > 0)
            sql += " and gc.id= " + gc_id;
        if (start != null && !start.isEmpty()) {
            sql += " and al.alarmTime>='" + start + "'";
        }
        if (end != null && !end.isEmpty()) {
            sql += " and al.alarmTime<'" + end + "'";
            ;
        }
        sql += " order by al.alarmTime desc";
        List<Map<String, Object>> data = getListMapBySql(sql, page, size);
        int total = GetCountBySql(sql);

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("total", total);
        map.put("data", data);
        return map;
    }

    public Map<String, Object> findCountBydepId(int depid) {
        String sql = "select al.alarmType,count(*) as al_count "
                + " from smk_alarm al left join smk_devices dv on al.smkDevicesId=dv.id "
                + "  left join sys_department dep on dv.departmentId=dep.id " + " where al.alarmStat=1 and (dep.id="
                + depid + " or dep.treeRoot like '%" + depid + ",%' ) group by al.alarmType";
        List<Map<String, Object>> data = getListMapBySql(sql);
        String sql1 = "SELECT count(*) as gc_count from smk_groupcompany gc "
                + "INNER JOIN sys_department d on d.id=gc.departmentId " + "where d.id=" + depid
                + " or d.treeRoot LIKE '%," + depid + ",%' ";
        Object o = GetSingleBySql(sql1);
        Map<String, Object> result = new HashMap<String, Object>();
        result.put("alarm", data);
        result.put("gc", o);
        return result;
    }

    @Override
    public Smk_Alarm findOne(Example<Smk_Alarm> example) {
        return _alarmJpa.findOne(example).orElse(null);
    }

    @Override
    public Smk_Alarm findOneById(int id) {
        return _alarmJpa.findById(id).orElse(null);
    }

    @Override
    public Smk_Alarm save(Smk_Alarm alarm) {
        if (alarm.getId() < 1)
            alarm.setJoinDate(new Date());
        else
            alarm.setUpdateDate(new Date());
        return _alarmJpa.save(alarm);
    }

    @Override
    public void deleteById(int id) {
        _alarmJpa.deleteById(id);
    }

    @Override
    public void updateDelete(int id) {
        Smk_Alarm a = _alarmJpa.findById(id).orElse(null);
        a.setDel(true);
        _alarmJpa.save(a);
    }

    @Override
    public Smk_Alarm findBySmkDevicesIdAndOid(int smkDevicesId, String oid) {
        return _alarmJpa.findBySmkDevicesIdAndOid(smkDevicesId, oid);
    }

    @Override
    public Map<String, Object> findByPageable_WX(String openId, int smkBuildingId, int alarmType, int alarmStat,
            String start, String end, int gc_id, int page, int size) {
        String sql = "select al.*,DATE_FORMAT(al.alarmTime,'%Y-%m-%d %H:%i:%S') alarmDate, dv.devName,dv.devId,dv.imei,bd.name as bdName,bd.addr as bdAddr,bd.managerName,bd.managerMobile,gc.groupName "
                + " from smk_alarm al left join smk_devices dv on al.smkDevicesId=dv.id "
                + " left join smk_buildings bd on bd.id = dv.smkBuildingId "
                + " left join smk_groupcompany gc on gc.id = bd.smkGroupCompanyId where bd.openid= '" + openId + "'";
        if (smkBuildingId > 0)
            sql += " and bd.id= " + smkBuildingId;
        if (alarmType > 0)
            sql += " and al.alarmType= " + alarmType;
        if (alarmStat > 0)
            sql += " and al.alarmStat= " + alarmStat;
        if (gc_id > 0)
            sql += " and gc.id= " + gc_id;
        if (start != null && !start.isEmpty()) {
            sql += " and al.alarmTime>='" + start + "'";
        }
        if (end != null && !end.isEmpty()) {
            sql += " and al.alarmTime<'" + end + "'";
            ;
        }
        sql += " order by al.alarmTime desc";
        List<Map<String, Object>> data = getListMapBySql(sql, page, size);
        int total = GetCountBySql(sql);

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("total", total);
        map.put("data", data);
        return map;
    }

    @Override
    public List<Map<String, Object>> findBySmkBuildingId(int smkBuildingId) {
        String sql = "select al.alarmDetail,al.alarmStat,DATE_FORMAT(al.alarmTime,'%Y-%m-%d %H:%i:%S') alarmDate,al.alarmType,al.dealMethod,al.dealResult,al.dealType,d.imei from smk_alarm al inner join "
                + " smk_devices d on al.smkDevicesId=d.id " + " where d.smkBuildingId=" + smkBuildingId;
        // String sql1 = "select new map(al.alarmType,al.alarmDetail) from Smk_Alarm al
        // inner join Smk_Devices d on al.smkDevicesId=d.id where
        // d.smkBuildingId="+smkBuildingId;
        List<Map<String, Object>> data = getListMapBySql(sql);
        // List<Map<String, Object>> data1 = getListMapByHql(sql1);
        return data;
    }

    @Override
    public Map<String, Object> findAlarmSumInfo(int depid) {
        String sql = "SELECT sum(case WHEN al.alarmType=1 and al.alarmStat=1 then 1 else 0 end ) as 'preAlarm', "
                + " sum(case WHEN al.alarmType=2 and al.alarmStat=1 then 1 else 0 end ) as 'fireAlarm', "
                + " sum(case WHEN al.alarmType=3 and al.alarmStat=1 then 1 else 0 end ) as 'errorAlarm',"
                + " sum(case WHEN al.alarmType=1 then 1 else 0 end ) as 'preAlarmHis', "
                + " sum(case WHEN al.alarmType=2 then 1 else 0 end ) as 'fireAlarmHis', "
                + " sum(case WHEN al.alarmType=3  then 1 else 0 end ) as 'errorAlarmHis'"
                + "  from smk_alarm al left join smk_devices dev" + " on al.smkDevicesId=dev.id"
                + "  LEFT JOIN sys_department dep" + " on dep.id=dev.departmentId" + "  where dep.id=" + depid
                + " or dep.treeRoot like '%" + depid + ",%'";
        List<Map<String, Object>> data = getListMapBySql(sql);
        return data.get(0);
    }

    @Override
    public List<Map<String, Object>> findAlarmStatGroupInfo(int depid) {
        String sql = "select (case al.alarmStat WHEN 1 Then '未确认' ELSE '已确认' END) alarmStat, "
                + " COUNT(al.alarmStat) count from smk_alarm al left join smk_devices dv on al.smkDevicesId=dv.id "
                + " left join sys_department dep on dv.departmentId=dep.id " + " where dep.id=" + depid
                + " or dep.treeRoot like '%" + depid + ",%' GROUP BY al.alarmStat";
        List<Map<String, Object>> data = getListMapBySql(sql);
        return data;
    }

    @Override
    public List<Map<String, Object>> findAlarmTypeGroupInfo(int depid) {
        String sql = "select (case al.alarmType WHEN 1 Then '预警' WHEN 2 then '火警' ELSE '故障' END) alarmType, "
                + " COUNT(al.alarmType) count from smk_alarm al left join smk_devices dv on al.smkDevicesId=dv.id "
                + " left join sys_department dep on dv.departmentId=dep.id " + " where dep.id=" + depid
                + " or dep.treeRoot like '%" + depid + ",%' GROUP BY al.alarmType";
        List<Map<String, Object>> data = getListMapBySql(sql);
        return data;
    }

    @Override
    public List<Map<String, Object>> findAlarmTypeFullYear(int depid) {
        Calendar date = Calendar.getInstance();
        String this_year = String.valueOf(date.get(Calendar.YEAR));
        String sql = "SELECT num.i as mon," + "case when t1.count1 is null then 0 ELSE t1.count1 end pre_count ,"
                + "case when t1.count2 is null then 0 ELSE t1.count2 end fire_count,"
                + "case when t1.count3 is null then 0 ELSE t1.count3 end break_count " + "from num left join ("
                + "select DATE_FORMAT(al.alarmTime,'%m') months,"
                + "sum(case when al.alarmType=1 then 1 else 0 end) count1, "
                + "sum(case when al.alarmType=2 then 1 else 0 end) count2, "
                + "sum(case when al.alarmType=3 then 1 else 0 end) count3 "
                + "from smk_alarm al left join smk_devices dv on al.smkDevicesId=dv.id left join sys_department dep on dv.departmentId=dep.id  "
                + "where dep.id= " + depid + " or dep.treeRoot like '%," + depid + ",%' and al.alarmTime>'" + this_year
                + "-01-01 00:00:00'" + "group by months" + ") t1 on num.i=t1.months order by i";
        List<Map<String, Object>> data = getListMapBySql(sql);
        return data;
    }

    @Override
    public List<Map<String, Object>> findAlarmTypeGroupByDep(int depid) {
        String sql = "select dep.depName," + "sum(case when al.alarmType=1 then 1 else 0 end) pre_count ,"
                + "sum(case when al.alarmType=2 then 1 else 0 end) fire_count,"
                + "sum(case when al.alarmType=3 then 1 else 0 end) break_count "
                + "from smk_alarm al left join smk_devices dv on al.smkDevicesId=dv.id left join sys_department dep on dv.departmentId=dep.id  "
                + "where dep.id= " + depid + " or dep.treeRoot like '%," + depid + ",%'group by dep.depName";
        List<Map<String, Object>> data = getListMapBySql(sql);
        return data;
    }

}